"""migrate favorites and ratings to user_ratings

Revision ID: d7c6efd2de42
Revises: 09aba125b57a
Create Date: 2024-03-18 02:28:15.896959

"""

from datetime import datetime, timezone
from textwrap import dedent
from typing import Any
from uuid import uuid4

import sqlalchemy as sa
from sqlalchemy import orm

import mealie.db.migration_types
from alembic import op

# revision identifiers, used by Alembic.
revision = "d7c6efd2de42"
down_revision = "09aba125b57a"
branch_labels = None
depends_on = None


def is_postgres():
    return op.get_context().dialect.name == "postgresql"


def new_user_rating(user_id: Any, recipe_id: Any, rating: float | None = None, is_favorite: bool = False):
    if is_postgres():
        id = str(uuid4())
    else:
        id = "%.32x" % uuid4().int  # noqa: UP031

    now = datetime.now(timezone.utc).isoformat()
    return {
        "id": id,
        "user_id": user_id,
        "recipe_id": recipe_id,
        "rating": rating,
        "is_favorite": is_favorite,
        "created_at": now,
        "update_at": now,
    }


def migrate_user_favorites_to_user_ratings():
    bind = op.get_bind()
    session = orm.Session(bind=bind)

    with session:
        user_ids_and_recipe_ids = session.execute(sa.text("SELECT user_id, recipe_id FROM users_to_favorites")).all()
        rows = [
            new_user_rating(user_id, recipe_id, is_favorite=True)
            for user_id, recipe_id in user_ids_and_recipe_ids
            if user_id and recipe_id
        ]

        if is_postgres():
            query = dedent(
                """
                INSERT INTO users_to_recipes (id, user_id, recipe_id, rating, is_favorite, created_at, update_at)
                VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at)
                ON CONFLICT DO NOTHING
                """
            )
        else:
            query = dedent(
                """
                INSERT OR IGNORE INTO users_to_recipes
                (id, user_id, recipe_id, rating, is_favorite, created_at, update_at)
                VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at)
                """
            )

        for row in rows:
            session.execute(sa.text(query), row)


def migrate_group_to_user_ratings(group_id: Any):
    bind = op.get_bind()
    session = orm.Session(bind=bind)

    with session:
        user_ids = (
            session.execute(sa.text("SELECT id FROM users WHERE group_id=:group_id").bindparams(group_id=group_id))
            .scalars()
            .all()
        )

        recipe_ids_ratings = session.execute(
            sa.text(
                "SELECT id, rating FROM recipes WHERE group_id=:group_id AND rating > 0 AND rating IS NOT NULL"
            ).bindparams(group_id=group_id)
        ).all()

        # Convert recipe ratings to user ratings. Since we don't know who
        # rated the recipe initially, we copy the rating to all users.
        rows: list[dict] = []
        for recipe_id, rating in recipe_ids_ratings:
            for user_id in user_ids:
                rows.append(new_user_rating(user_id, recipe_id, rating, is_favorite=False))

        if is_postgres():
            insert_query = dedent(
                """
                INSERT INTO users_to_recipes (id, user_id, recipe_id, rating, is_favorite, created_at, update_at)
                VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at)
                ON CONFLICT (user_id, recipe_id) DO NOTHING;
                """
            )
        else:
            insert_query = dedent(
                """
                INSERT OR IGNORE INTO users_to_recipes
                (id, user_id, recipe_id, rating, is_favorite, created_at, update_at)
                VALUES (:id, :user_id, :recipe_id, :rating, :is_favorite, :created_at, :update_at);
                """
            )

        update_query = dedent(
            """
            UPDATE users_to_recipes
            SET rating = :rating, update_at = :update_at
            WHERE user_id = :user_id AND recipe_id = :recipe_id;
            """
        )

        # Create new user ratings with is_favorite set to False
        for row in rows:
            session.execute(sa.text(insert_query), row)

        # Update existing user ratings with the correct rating
        for row in rows:
            session.execute(sa.text(update_query), row)


def migrate_to_user_ratings():
    migrate_user_favorites_to_user_ratings()

    bind = op.get_bind()
    session = orm.Session(bind=bind)

    with session:
        group_ids = session.execute(sa.text("SELECT id FROM groups")).scalars().all()

    for group_id in group_ids:
        migrate_group_to_user_ratings(group_id)


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "users_to_recipes",
        sa.Column("user_id", mealie.db.migration_types.GUID(), nullable=False),
        sa.Column("recipe_id", mealie.db.migration_types.GUID(), nullable=False),
        sa.Column("rating", sa.Float(), nullable=True),
        sa.Column("is_favorite", sa.Boolean(), nullable=False),
        sa.Column("id", mealie.db.migration_types.GUID(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=True),
        sa.Column("update_at", sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(
            ["recipe_id"],
            ["recipes.id"],
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["users.id"],
        ),
        sa.PrimaryKeyConstraint("user_id", "recipe_id", "id"),
        sa.UniqueConstraint("user_id", "recipe_id", name="user_id_recipe_id_rating_key"),
    )
    op.create_index(op.f("ix_users_to_recipes_created_at"), "users_to_recipes", ["created_at"], unique=False)
    op.create_index(op.f("ix_users_to_recipes_is_favorite"), "users_to_recipes", ["is_favorite"], unique=False)
    op.create_index(op.f("ix_users_to_recipes_rating"), "users_to_recipes", ["rating"], unique=False)
    op.create_index(op.f("ix_users_to_recipes_recipe_id"), "users_to_recipes", ["recipe_id"], unique=False)
    op.create_index(op.f("ix_users_to_recipes_user_id"), "users_to_recipes", ["user_id"], unique=False)

    migrate_to_user_ratings()

    if is_postgres():
        op.drop_index("ix_users_to_favorites_recipe_id", table_name="users_to_favorites")
        op.drop_index("ix_users_to_favorites_user_id", table_name="users_to_favorites")
        op.alter_column("recipes", "rating", existing_type=sa.INTEGER(), type_=sa.Float(), existing_nullable=True)
    else:
        op.execute("DROP INDEX IF EXISTS ix_users_to_favorites_recipe_id")
        op.execute("DROP INDEX IF EXISTS ix_users_to_favorites_user_id")
        with op.batch_alter_table("recipes") as batch_op:
            batch_op.alter_column("rating", existing_type=sa.INTEGER(), type_=sa.Float(), existing_nullable=True)

    op.drop_table("users_to_favorites")
    op.create_index(op.f("ix_recipes_rating"), "recipes", ["rating"], unique=False)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column(
        "recipes_ingredients", "quantity", existing_type=sa.Float(), type_=sa.INTEGER(), existing_nullable=True
    )
    op.drop_index(op.f("ix_recipes_rating"), table_name="recipes")
    op.alter_column("recipes", "rating", existing_type=sa.Float(), type_=sa.INTEGER(), existing_nullable=True)
    op.create_unique_constraint("ingredient_units_name_group_id_key", "ingredient_units", ["name", "group_id"])
    op.create_unique_constraint("ingredient_foods_name_group_id_key", "ingredient_foods", ["name", "group_id"])
    op.create_table(
        "users_to_favorites",
        sa.Column("user_id", sa.CHAR(length=32), nullable=True),
        sa.Column("recipe_id", sa.CHAR(length=32), nullable=True),
        sa.ForeignKeyConstraint(
            ["recipe_id"],
            ["recipes.id"],
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["users.id"],
        ),
        sa.UniqueConstraint("user_id", "recipe_id", name="user_id_recipe_id_key"),
    )
    op.create_index("ix_users_to_favorites_user_id", "users_to_favorites", ["user_id"], unique=False)
    op.create_index("ix_users_to_favorites_recipe_id", "users_to_favorites", ["recipe_id"], unique=False)
    op.drop_index(op.f("ix_users_to_recipes_user_id"), table_name="users_to_recipes")
    op.drop_index(op.f("ix_users_to_recipes_recipe_id"), table_name="users_to_recipes")
    op.drop_index(op.f("ix_users_to_recipes_rating"), table_name="users_to_recipes")
    op.drop_index(op.f("ix_users_to_recipes_is_favorite"), table_name="users_to_recipes")
    op.drop_index(op.f("ix_users_to_recipes_created_at"), table_name="users_to_recipes")
    op.drop_table("users_to_recipes")
    # ### end Alembic commands ###
